/**
 * mysql 初始化文件
 */
var pool = require("../db/sqlpool");


class Mysql {
    table: string
    wheres: string
    limits: string
    orders: string
    columns: string = ''
    print: boolean = false

    /**
     * 构造函数
     * @param table 表名
     * @param wheres 条件
     * @param limits 限制结果数量
     * @param sets 更新时的 value
     */
    constructor(table: string, print?: boolean,wheres?: string, limits?: string, orders?: string) {
        this.table = table
        this.wheres = wheres as string
        this.limits = limits as string
        this.orders = orders as string
        this.print = print as boolean
    }

    /**
     * 拼接 where 条件
     * @param arr 
     * @returns 
     */
    where(arr: object, lian: string = 'and') {
        let arrs: string[] = []
        Object.keys(arr).forEach((item: any, index: any) => {
            Object.values(arr).forEach((items: any, idx: any) => {
                if (index === idx) {
                    if (typeof (items) === 'string') items = `'${items}'`
                    if(['number','string'].includes(typeof items)){
                        arrs.push(item + '=' + items)
                    }else if(Array.isArray(items) && items[0] && items[1]){
                        arrs.push(item + ' ' + items[0] + ' ' + items[1] )
                    }else if(typeof items == 'object'){
                        arrs.push('( '+this.where(items,` ${item} `)+' )')
                    }
                }
            })
        })
        //避免如 id in ('1','2')条件时逗号冲突
        this.wheres = arrs.join("$￥$").replace(/\$\￥\$/g, ` ${lian} `)
        return this
    }

    /**
     * 查询的字段
     * @returns 
     */
    fields(columns: string) {
        if (columns) this.columns = columns
        return this
    }

    /**
     * 单条数据查询
     * @returns 
     */
    find() {
        this.limits = `limit 1`
        var sql: string = this.columns ? 'select ' + this.columns : 'select *'
        if (this.table) sql += ` from ${this.table}`
        if (this.wheres) sql += ` where ${this.wheres}`
        if (this.limits) sql += ` ${this.limits}`
        this.columns = this.limits = ''
        if(this.print) console.log(sql)
        return querys(sql, 'find')
    }

    /**
     * 多条数据查询
     * @returns 
     */
    select() {
        var sql: string = this.columns ? 'select ' + this.columns : 'select *'
        if (this.table) sql += ` from ${this.table}`
        if (this.wheres) sql += ` where ${this.wheres}`
        if (this.orders) sql += ` ${this.orders}`
        if (this.limits) sql += ` ${this.limits}`
        this.columns = this.orders = this.limits = ''
        if(this.print) console.log(sql)
        return querys(sql, 'select')
    }

    /**
     * 数据更新
     * @param arr 
     * @returns 
     */
    update(...arr: any) {
        arr = arr[0]
        let arrs: string[] = []
        Object.keys(arr).forEach((item: any, index: any) => {
            Object.values(arr).forEach((items: any, idx: any) => {
                if (index === idx) {
                    if (typeof (items) === 'string') items = `'${items.replace(/'/g,"\\'")}'`
                    arrs.push(item + '=' + items)
                }
            })
        })
        let sets = arrs.toString()
        var sql: string = 'update'
        if (this.table) sql += ` ${this.table}`
        if (sets) sql += ` set ${sets}`
        if (this.wheres) sql += ` where ${this.wheres}`
        if(this.print) console.log(sql)
        return querys(sql)
    }

    /**
     * 数据删除
     * @returns 
     */
    delete() {
        var sql: string = 'delete'
        if (this.table) sql += ` from ${this.table}`
        if (this.wheres) sql += ` where ${this.wheres}`
        if(this.print) console.log(sql)
        return querys(sql)
    }

    /**
     * 新增数据
     * @param arr 
     */
    insert(...arr: any) {
        arr = arr[0]
        let key: string[] = [], val: string[] = []
        Object.keys(arr).forEach((item: any) => {
            key.push(item)
        })

        Object.values(arr).forEach((item: any) => {
            if (typeof (item) === 'string') item = `'${item.replace(/'/g,"\\'")}'`
            val.push(item)
        })
        let keys = key.toString()
        let vals = val.toString()
        var sql: string = 'insert into'
        if (this.table) sql += ` ${this.table}`
        if (keys) sql += ` (${keys})`
        if (vals) sql += ` values (${vals})`
        if(this.print) console.log(sql)
        return querys(sql)
    }

    /**
     * 结果排序
     * @param arr 
     * @returns 
     */
    order(...arr: any) {
        arr = arr[0]
        let keys, vals
        Object.keys(arr).forEach(item => {
            keys = item
        })
        Object.values(arr).forEach(item => {
            vals = item
        })
        this.orders = `ORDER BY ${keys} ${vals}`
        return this
    }

    /**
     * 分页处理
     * @param page 
     * @returns 
     */
    limit(limit: number = 0,size: number = 10){
        this.limits += ` limit ${limit}, ${size} `
        return this
    }
}

/**
 * 执行 sql 
 * @param sql sql 语句
 * @param type 查询方式 find 单条 select 多条
 * @returns 
 */
let querys = (sql: string, type?: string) => {
    return new Promise((resolve) => {
        pool.getConnection(function (err: any, connection: any) {
            if (err) throw err;
            connection.query(sql, (err: any, res: any) => {
                if (err) {
                    return err
                }
                if (type === 'find') resolve(res[0])
                if (type === 'select') resolve(res)
                if (!type) resolve(res)
            })
            connection.release();
        })
    })
}

module.exports = {
    db(table: string,print: boolean) {
        return new Mysql(table,print)
    }
}